package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.Project;
import com.zzmcc.sys.model.Userinfo;
import util.ChristStringUtil;
import util.DateUtil;
import util.ModelUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.zzmcc.jms.common.ModelConfigWms.TABLE_Project;


public class ProjectController0908 extends BaseController {
    private static final Logger log =  Logger.getLogger(ProjectController0908.class);

	public void index() {

        StringBuffer whee = new StringBuffer();
        List<Object> param = new ArrayList<Object>();
        String name = getPara("project.name");
		String code = getPara("project.code");
		String isProject = getPara("project.isProject");
		String isPlus = getPara("project.isPlus");
		String share = getPara("project.share");
		String isDemand = getPara("project.isDemand");
		String isSelectConfirm = getPara("project.isSelectConfirm");
		String demandOwner = getPara("project.demandOwner");
		String demandMemo = getPara("project.demandMemo");
        String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String homeCounty = getPara("project.homeCounty");

        if(ChristStringUtil.isNotEmpty(name)){
            whee.append(" and name like ?");
            param.add("%" + name + "%");
        }
		if(ChristStringUtil.isNotEmpty(code)){
			whee.append(" and code like ?");
			param.add("%" + code + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner like ?");
			param.add("%" + demandOwner + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}
		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}


		if(ChristStringUtil.isNotEmpty(isDemand)){

			if (isDemand.equals("是")){
				whee.append(" and (a.isCm='是' or isCt='是' or isCu='是') ");
			}else{
				whee.append(" and coalesce(isCm,'否')='否' and coalesce(isCt,'否')='否' and coalesce(isCu,'否')='否' ");
			}

		}

		if(ChristStringUtil.isNotEmpty(isProject)){
			whee.append(" and coalesce(isProject,'否') = ?");
			param.add(isProject);
		}

		if(ChristStringUtil.isNotEmpty(isPlus)){
			whee.append(" and coalesce(isPlus,'否') = ?");
			param.add(isPlus);
		}

		if(ChristStringUtil.isNotEmpty(share)){
			whee.append(" and share = ?");
			param.add(share);
		}
        if(ChristStringUtil.isNotEmpty(beginDate)){
            whee.append(" and TO_DAYS(projectDate) >= TO_DAYS(?)");
            param.add( beginDate );
        }
		if(ChristStringUtil.isNotEmpty(endDate)){
			whee.append(" and TO_DAYS(projectDate) <= TO_DAYS(?)");
			param.add(endDate);
		}
		if(ChristStringUtil.isNotEmpty(isSelectConfirm)){
			whee.append(" and coalesce(isSelectConfirm,'否') = ?");
			param.add(isSelectConfirm);
		}
		String fromStr = " from " + TABLE_Project +" a where 1=1 "+ whee.toString()+" order by modifyDate desc";
		Page<Project> pager = Project.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
                "select * ",fromStr,
                param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("demandOwner",demandOwner);
		setAttr("demandMemo",demandMemo);
		setAttr("isDemand",isDemand);
		setAttr("isProject",isProject);
		setAttr("isPlus",isPlus);
		setAttr("share",share);
		setAttr("isSelectConfirm",isSelectConfirm);
		setAttr("beginDate",beginDate);
		setAttr("endDate",endDate);
		setAttr("homeCounty",homeCounty);
		render("../project_list.html");

	}

	public void save() {
		Project s = getModel(Project.class, "project");
		s.save();
		renderDWZSuccessJson("添加成功！");
	}

	@Before(AuthorizeInterceptor.class)
	public void edit() {
		setAttr("project",Project.dao.findById(getPara("id")));
		render("../project_input.html");
	}
	
	public void update() {
		Project s = getModel(Project.class, "project");
		s.update();
		renderDWZSuccessJson("修改成功！");
	}

	@Before(AuthorizeInterceptor.class)
	public void delete() {
		Userinfo loginUser =getSessionAttr("loginUser");
		String modifyUser=loginUser.get("realname");
		String[] ids = getParaValues("ids");
		for(String id:ids){
			Project m=Project.dao.findById(id);
			m.set("isSelectConfirm","否");
			m.set("modifyUser",modifyUser);
			ModelUtils.fillForUpdate(m);
			m.update();
		}
		renderDWZSuccessJson("删除成功!");
	}

	public void exportDetail() {

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String isProject = getPara("project.isProject");
		String demandOwner = getPara("project.demandOwner");
		String demandMemo = getPara("project.demandMemo");

		if(ChristStringUtil.isNotEmpty(isProject)){
			whee.append(" and isProject = ?");
			param.add(isProject);
		}
		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner = ?");
			param.add( demandOwner);
		}

		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		String sql = "select a.homeCounty,a.code,a.name,a.cmName,a.ctName,a.cuName,a.planLong,a.planLat,\n" +
				"a.isCm,a.isCt,a.isCu,a.cmCreateDate,a.ctCreateDate,a.cuCreateDate,a.cmSubmitDate,a.ctSubmitDate,a.cuSubmitDate,\n" +
				"a.cmSource,a.ctSource,a.cuSource,a.demandOwner,a.isSelect,\n" +
				"a.towerType,a.longitude,a.latitude,a.addr,\n" +
				"a.isCmOrderConfirm,a.isCtOrderConfirm,a.isCuOrderConfirm,\n" +
				"a.share,a.shareName,a.isSelectConfirm,a.isProject,a.projectDate,a.isCancel,a.demandMemo,\n" +
				"b.isReach,b.isStart,b.isDesign,b.isComplete,b.isSubmit,\n" +
				"b.modifyDate bmodifyDate,b.modifyMemo bmodifyMemo,b.memo bmemo,a.projectOwner,\n" +
				"b.planCompleteDate,b.completeDate,\n" +
				"a.designUnit,a.buildUnit,a.auxUnit,a.inspectUnit,a.memo \n" +
				"from jms_project a\n" +
				"left join jms_project_build b\n" +
				"on a.pid=b.pid" +
				" where 1=1 "+ whee.toString()+"order by a.id ";
		List<Record> projectDetail = Db.find(sql, param.toArray());

		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("projectDetail", projectDetail);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project.xls";
		String filename = "project.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));


	}

	@Before(AuthorizeInterceptor.class)
	public void exportSum() {

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String isPlus= getPara("project.isPlus");

		if(ChristStringUtil.isNotEmpty(isPlus)){
			whee.append(" and isPlus = ?");
			param.add(isPlus);
		}

		String sql="select date_format(now(),'%Y%m%d') sumDate,homeCounty,selectCnt,\n" +
				"projectCnt,notprojectCnt,projectReachCnt,notprojectReachCnt,\n" +
				"cmCnt,cmOrderCnt,cmSubmitCnt,cmRentCnt,\n" +
				"ctCnt,ctOrderCnt,ctSubmitCnt,ctRentCnt,\n" +
				"cuCnt,cuOrderCnt,cuSubmitCnt,cuRentCnt,\n" +
				"designCnt,startCnt,completeCnt,submitCnt\n" +
				"from (\n" +
				"select a.homeCounty,count(*) cnt,\n" +
				"sum(case when a.isCm='是' then 1 else 0 end) cmCnt,\n" +
				"sum(case when a.isCmOrderConfirm='是' then 1 else 0 end) cmOrderCnt,\n" +
				"sum(case when c.isCmSubmit='是'  then 1 else 0 end) cmSubmitCnt,\n" +
				"sum(case when c.isCmRent='是'  then 1 else 0 end) cmRentCnt,\n" +
				"sum(case when a.isCt='是' then 1 else 0 end) ctCnt,\n" +
				"sum(case when a.isCtOrderConfirm='是' then 1 else 0 end) ctOrderCnt,\n" +
				"sum(case when c.isCtSubmit='是'  then 1 else 0 end) ctSubmitCnt,\n" +
				"sum(case when c.isCtRent='是'  then 1 else 0 end) ctRentCnt,\n" +
				"sum(case when a.isCu='是' then 1 else 0 end) cuCnt,\n" +
				"sum(case when a.isCuOrderConfirm='是' then 1 else 0 end) cuOrderCnt,\n" +
				"sum(case when c.isCuSubmit='是'  then 1 else 0 end) cuSubmitCnt,\n" +
				"sum(case when c.isCuRent='是'  then 1 else 0 end) cuRentCnt,\n" +
				"sum(case when a.isProject='否' then 1 else 0 end) notprojectCnt,\n" +
				"sum(case when a.isProject='否' and a.isSelectConfirm='否' then 1 else 0 end) selectCnt,\n" +
				"sum(case when a.isProject='否' and COALESCE(b.isReach,'否')='否' then 1 else 0 end) notprojectReachCnt,\n" +
				"sum(case when a.isProject='是' then 1 else 0 end) projectCnt,\n" +
				"sum(case when a.isProject='是' and COALESCE(b.isReach,'否')='否' then 1 else 0 end) projectReachCnt,\n" +
				"sum(case when a.isProject='是' and b.isDesign='是' then 1 else 0 end) designCnt,\n" +
				"sum(case when a.isProject='是' and b.isStart='是' then 1 else 0 end) startCnt,\n" +
				"sum(case when a.isProject='是' and b.isComplete='是' then 1 else 0 end) completeCnt,\n" +
				"sum(case when a.isProject='是' and b.isSubmit='是' then 1 else 0 end) submitCnt \n" +
				"  from jms_project a left join jms_project_build b\n" +
				"on a.pid=b.pid\n" +
				"  left join jms_project_submit c\n" +
				"on a.code=c.code\n" +
				"where a.isTask='是' \n" + whee.toString() +
				"group by a.homeCounty\n" +
				")t\n" +
				"\n";
		List<Record> projectSum = Db.find(sql, param.toArray());
		//统计入库
		String sumDate = DateUtil.getStringDateShort();
		Db.update("delete a from jms_project_demand_sum a where sumDate =?",sumDate);
		Record rMax = Db.findFirst("select max(sumDate) lastDate from jms_project_demand_sum");
		String lastDate=rMax.get("lastDate");
		for(Record r:projectSum){
			Db.save("jms_project_demand_sum",r);
		}
		/*
   		0、汇总
   		*/
		sql="select a.pCnt," +
				"a.prCnt,(b.prCnt-a.prCnt) addPrCnt,\n" +
				"a.submitCnt,(a.submitCnt-b.submitCnt) addSubmitCnt,\n" +
				"a.npCnt,a.selCnt,(b.selCnt-a.selCnt) addSelCnt," +
				"a.nprCnt,(a.nprCnt-b.nprCnt) addNprCnt,\n" +
				"a.cmCnt,(a.cmCnt-b.cmCnt) addCmCnt," +
				"a.cmOrderCnt,(a.cmOrderCnt-b.cmOrderCnt) addCmOrderCnt," +
				"a.cmSubmitCnt,(a.cmSubmitCnt-b.cmSubmitCnt) addCmSubmitCnt," +
				"a.cmRentCnt,(a.cmRentCnt-b.cmRentCnt) addCmRentCnt,\n" +
				"a.ctCnt,(a.ctCnt-b.ctCnt) addCtCnt," +
				"a.ctOrderCnt,(a.ctOrderCnt-b.ctOrderCnt) addCtOrderCnt," +
				"a.ctSubmitCnt,(a.ctSubmitCnt-b.ctSubmitCnt) addCtSubmitCnt," +
				"a.ctRentCnt,(a.ctRentCnt-b.ctRentCnt) addCtRentCnt,\n" +
				"a.cuCnt,(a.cuCnt-b.cuCnt) addCuCnt," +
				"a.cuOrderCnt,(a.cuOrderCnt-b.cuOrderCnt) addCuOrderCnt," +
				"a.cuSubmitCnt,(a.cuSubmitCnt-b.cuSubmitCnt) addCuSubmitCnt," +
				"a.cuRentCnt,(a.cuRentCnt-b.cuRentCnt) addCuRentCnt\n" +
				"from \n" +
				"(select sum(projectCnt) pCnt,sum(projectReachCnt) prCnt,sum(submitCnt) submitCnt,\n" +
				"sum(notprojectCnt) npCnt,sum(selectCnt) selCnt,sum(notprojectReachCnt) nprCnt,\n" +
				"sum(cmCnt) cmCnt,sum(cmOrderCnt) cmOrderCnt,sum(cmSubmitCnt) cmSubmitCnt,sum(cmRentCnt) cmRentCnt,\n" +
				"sum(ctCnt) ctCnt,sum(ctOrderCnt) ctOrderCnt,sum(ctSubmitCnt) ctSubmitCnt,sum(ctRentCnt) ctRentCnt,\n" +
				"sum(cuCnt) cuCnt,sum(cuOrderCnt) cuOrderCnt,sum(cuSubmitCnt) cuSubmitCnt,sum(cuRentCnt) cuRentCnt\n" +
				"  from jms_project_demand_sum\n" +
				"where sumDate="+sumDate +
				") a,\n" +
				"(select sum(projectCnt) pCnt,sum(projectReachCnt) prCnt,sum(submitCnt) submitCnt,\n" +
				"sum(notprojectCnt) npCnt,sum(selectCnt) selCnt,sum(notprojectReachCnt) nprCnt,\n" +
				"sum(cmCnt) cmCnt,sum(cmOrderCnt) cmOrderCnt,sum(cmSubmitCnt) cmSubmitCnt,sum(cmRentCnt) cmRentCnt,\n" +
				"sum(ctCnt) ctCnt,sum(ctOrderCnt) ctOrderCnt,sum(ctSubmitCnt) ctSubmitCnt,sum(ctRentCnt) ctRentCnt,\n" +
				"sum(cuCnt) cuCnt,sum(cuOrderCnt) cuOrderCnt,sum(cuSubmitCnt) cuSubmitCnt,sum(cuRentCnt) cuRentCnt\n" +
				"  from jms_project_demand_sum\n" +
				"where sumDate="+ lastDate +
				") b";
		List<Record> cal = Db.find(sql);

		/*
   		1、未立项未筛查排名
   		*/
		sql="SELECT concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,selectCnt," +
				" (@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate = ? \n" +
				"ORDER BY selectCnt desc \n" +
				")t";
		List<Record> selSort = Db.find(sql,sumDate);

		/*
   		2、未立项未谈点排名
   		*/
		sql="SELECT  concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,notprojectCnt,notprojectReachCnt, \n" +
				"(@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate = ?\n" +
				"ORDER BY notprojectReachCnt desc\n" +
				")t";
		List<Record> nprSort = Db.find(sql,sumDate);

		/*
   		21、移动需求转订单排名
   		*/
		sql="SELECT concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,cmOrderCnt/cmCnt cmOrderRate, \n" +
				"(@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where cmCnt>0 and sumDate = ?\n" +
				"ORDER BY cmOrderCnt/cmCnt  \n" +
				")t";
		List<Record> cmoSort = Db.find(sql,sumDate);

		/*
   		22、移动起租率排名
   		*/
		sql="SELECT concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,cmRentCnt/cmSubmitCnt cmRentRate, \n" +
				"(@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where cmSubmitCnt>0 and sumDate = ?\n" +
				"ORDER BY cmRentCnt/cmSubmitCnt  \n" +
				")t";
		List<Record> cmrSort = Db.find(sql,sumDate);

		/*
   		31、电信需求转订单排名
   		*/
		sql="SELECT concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,ctOrderCnt/ctCnt ctOrderRate, \n" +
				"(@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where ctCnt>0 and sumDate = ?\n" +
				"ORDER BY ctOrderCnt/ctCnt  \n" +
				")t";
		List<Record> ctoSort = Db.find(sql,sumDate);

		/*
   		32、电信起租率排名
   		*/
		sql="SELECT concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,ctSubmitCnt/ctRentCnt cmRentRate, \n" +
				"(@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where ctSubmitCnt>0 and sumDate = ?\n" +
				"ORDER BY ctRentCnt/ctSubmitCnt  \n" +
				")t";
		List<Record> ctrSort = Db.find(sql,sumDate);


		/*
   		41、联通需求转订单排名
   		*/
		sql="SELECT concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,cuOrderCnt/cuCnt cmOrderRate, \n" +
				"(@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where cuCnt>0 and sumDate = ?\n" +
				"ORDER BY cuOrderCnt/cuCnt  \n" +
				")t";
		List<Record> cuoSort = Db.find(sql,sumDate);

		/*
   		2、联通起租率排名
   		*/
		sql="SELECT concat(max(case when selSort=1 then homeCounty end),'、',\n" +
				"max(case when selSort=2 then homeCounty end),'、',\n" +
				"max(case when selSort=3 then homeCounty end)) homeCounty\n" +
				"from(\n" +
				"select homeCounty,cuRentCnt/cuSubmitCnt cuRentRate, \n" +
				"(@rowno:=@rowno+1) selSort from jms_project_demand_sum m,(select (@rowno:=0) ) n\n" +
				"where cuSubmitCnt>0 and sumDate = ?\n" +
				"ORDER BY cuRentCnt/cuSubmitCnt  \n" +
				")t";
		List<Record> curSort = Db.find(sql,sumDate);

		Map<String, Object> beans = new HashMap<String, Object>();

		beans.put("projectSum", projectSum);
		beans.put("sumDate",sumDate);
		beans.put("lastDate",lastDate);
		beans.put("cal",cal);
		beans.put("selSort",selSort);
		beans.put("nprSort",nprSort);

		beans.put("cmoSort",cmoSort);
		beans.put("cmrSort",cmrSort);
		beans.put("ctoSort",ctoSort);
		beans.put("ctrSort",ctrSort);
		beans.put("cuoSort",cuoSort);
		beans.put("curSort",curSort);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project.xls";
		String filename = "project.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));


	}

}
